<?xml version="1.0" encoding="utf-8"?>
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="statements.xsd">
	<module name="authority">
		<table name="user" description="用户信息表" >
			<column name="user_id" primaryKey="true" unique="true" type="string" defaultValue="" />
			<column name="user_name" unique="true" type="string" defaultValue=""/>
			<column name="real_name" type="string" />
			<column name="dep_id" type="string"/>
			<column name="dep_name" type="string"/>
			<column name="role_id" type="string"/>
			<column name="role_name" type="string"/>
			<column name="status" type="int"/>
		</table>
		<table name="change_password" description="修改密码表" >
			<column name="user_name" type="string" unique="true" description="用户名" />
			<column name="password" type="string" description="原密码" />
			<column name="new_password" type="string" description="新密码" />
		</table>
		<table name="role" description="角色信息" >
			<column name="role_id" primaryKey="true" unique="true" type="string" defaultValue="" />
			<column name="role_name" type="string" />
			<column name="role_seq" type="int"/>
			<column name="role_actions" type="string" />
			<column name="display_name" type="string" />
			<column name="description" type="string" />
		</table>
		<statement name="validate" resultType="bool" >
			<![CDATA[
			select exists(
			select sec_pm.user_id userId from sec_pm
			join sec_user on sec_pm.user_id = sec_user.user_id
			where sec_user.name = #user_name#
			and sec_pm.password = #password#
			and sec_user.`status` = 1);
			]]>
			<parameter name="user_name" type="string" />
			<parameter name="password" type="string" />
		</statement>

		<statement name="validate2" resultType="bool" >
			<![CDATA[
			select exists(
			select sec_pm.user_id userId from sec_pm
			join sec_user on sec_pm.user_id = sec_user.user_id
			where sec_user.`user_id` = #user_id#
			and sec_pm.password = #password#
			and sec_user.`status` = 1);
			]]>
			<parameter name="user_id" type="string" />
			<parameter name="password" type="string" />
		</statement>

		<statement name="find_users" allowPaging="true" resultType="table" resultTable="user">
			<![CDATA[
			select  userId, `name` as userName, realName, `status`, depId, depName, group_concat(roleId) as roleId, group_concat(roleName) as roleName
			from secUserInfo
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="user_name">
					<![CDATA[
					`name` like concat('%', #user_name#, '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="real_name">
					<![CDATA[
					realName like concat('%', #real_name#, '%')
					]]>
				</isNotEmpty>
				<isNotNull parameter="status">
					<![CDATA[
					status = #status#
					]]>
				</isNotNull>
				<isNotEmpty parameter="dep_id">
					<![CDATA[
					depId = #dep_id# 
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="role_id">
					<![CDATA[
					roleId  in ('$role_id$')
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
			group by `userId`
			order by convert(`name` using gbk)
			]]>
			<parameter name="user_name" type="string" matchType="contains"/>
			<parameter name="real_name" type="string" matchType="contains"/>
			<parameter name="status" type="int" />
			<parameter name="dep_id" type="string" />
			<parameter name="role_id" type="string" />
		</statement>

		<statement name="find_users_count"  resultType="int">
			<![CDATA[
			select  
			count(distinct userId)
			from 
			secUserInfo 
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="user_name">
					<![CDATA[
					`name` like concat('%', #user_name#, '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="real_name">
					<![CDATA[
					realName like concat('%', #real_name#, '%')
					]]>
				</isNotEmpty>
				<isNotNull parameter="status">
					<![CDATA[
					status = #status#
					]]>
				</isNotNull>
				<isNotEmpty parameter="dep_id">
					<![CDATA[
					depId = #dep_id# 
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="role_id">
					<![CDATA[
					roleId  in ('$role_id$')
					]]>
				</isNotEmpty>
			</dynamic>
			<parameter name="user_name" type="string" matchType="contains"/>
			<parameter name="real_name" type="string" matchType="contains"/>
			<parameter name="status" type="int" />
			<parameter name="dep_id" type="string" />
			<parameter name="role_id" type="string" />
		</statement>

		<statement name="find_user_by_id" resultType="table" resultTable="user">
			<![CDATA[
			select  userId, `name` as userName, realName, `status`, depId, depName, group_concat(roleId) as roleId, group_concat(roleName) as roleName
			from secUserInfo 
			where userId = #user_id#
			group by `userId`
			limit 1
			]]>
			<parameter name="user_id" type="string" />
		</statement>

		<statement name="find_user_by_name" resultType="table" resultTable="user">
			<![CDATA[
			select  userId, `name` as userName, realName, `status`, depId, depName, group_concat(roleId) as roleId, group_concat(roleName) as roleName
			from secUserInfo 
			where `name` = #user_name#
			group by `userId`
			]]>
			<parameter name="user_name" type="string" />
		</statement>

		<statement name="find_roles" resultType="table" resultTable="role" description="获取维护的所有角色信息">
			<![CDATA[
			select 
			role_id as roleId,
			display_name as roleName,
			role_seq as roleSeq,
			description
			from sec_role
			order by convert(roleName using gbk);
			]]>
		</statement>

		<statement name="find_actions" resultType="table" >
			<![CDATA[
			select
			action_id as  actionId,
			`name`,
			description,
			action_page as actionPage,
			seq
			from sec_action
			order by convert(`name` using gbk)
			]]>
		</statement>

		<statement name="validate_duplicated_user" resultType="bool" >
			<![CDATA[
			select exists(
			select sec_user.user_id userId from sec_user
			where sec_user.name = #user_name#
			and sec_user.user_id <> #user_id#)
			]]>
			<parameter name="user_name" type="string" />
			<parameter name="user_id" type="string" />
		</statement>

		<statement name="create_user">
			<![CDATA[
			insert into sec_user
			(user_id, `name`, real_name, seq, `status`)
			select #user_id#, #user_name#, #real_name#, ifnull(max(seq), 0) + 1, #status#
			from sec_user;
			insert into sec_pm
			(user_id, password)
			values(#user_id#, #password#);
			]]>
			<parameter name="user_id" type="string" />
			<parameter name="user_name" type="string" />
			<parameter name="real_name" type="string" />
			<parameter name="password" type="string" />
			<parameter name="status" type="int" />
		</statement>

		<statement name="update_user">
			<![CDATA[
			update sec_user
			set `name` = #user_name#,
			real_name = #real_name#
			where user_id = #user_id#
			]]>
			<parameter name="user_id" type="string" />
			<parameter name="user_name" type="string" />
			<parameter name="real_name" type="string" />
		</statement>

		<statement name="disable_user">
			<![CDATA[
			update sec_user set `status` = 0 where user_id = #user_id#]]>
			<parameter name="user_id" type="string" />
		</statement>

		<statement name="enable_user">
			<![CDATA[
			update sec_user set `status` = 1 where user_id = #user_id#]]>
			<parameter name="user_id" type="string" />
		</statement>

		<statement name="change_password">
			<![CDATA[
			update sec_pm join sec_user su on su.user_id = sec_pm.user_id
			set `password` = #new_password#
			where su.`user_id` = #user_id#
			]]>
			<parameter name="user_id" type="string" />
			<parameter name="new_password" type="string" />
		</statement>

		<statement name="remove_user">
			<![CDATA[
			delete from msg_user where user_id = #user_id#;
			update project set user_id = NULL where user_id = #user_id#;
			update project_check set user_id = NULL where user_id = #user_id#;
			update project_apply_check set user_id = NULL where user_id = #user_id#;
			update sys_log set user_id = NULL where user_id = #user_id#;
			delete from entity_auth_item where user_id = #user_id#;
			delete from sec_pm where user_id = #user_id#;
			delete from sec_user_role where user_id = #user_id#;
			delete from sec_user_ext where user_id = #user_id#;
			delete from sec_user where user_id = #user_id# limit 1;
			]]>
			<parameter name="user_id" type="string" />
		</statement>


		<statement name="delete_user_role">
			<![CDATA[
			delete from sec_user_role where user_id = #user_id#;
			]]>
			<parameter name="user_id" type="string" />
		</statement>

		<statement name="create_user_role">
			<![CDATA[
			insert into sec_user_role
			(user_id, role_id)
			values(#user_id#, #role_id#);
			]]>
			<parameter name="user_id" type="string" />
			<parameter name="role_id" type="string" />
		</statement>

		<statement name="delete_user_dep">
			<![CDATA[
			delete from sec_user_ext where user_id = #user_id#;
			]]>
			<parameter name="user_id" type="string" />
		</statement>

		<statement name="create_user_dep">
			<![CDATA[
			insert into sec_user_ext
			(user_id, dep_id)
			values(#user_id#, #dep_id#);
			]]>
			<parameter name="user_id" type="string" />
			<parameter name="dep_id" type="string" />
		</statement>

		<statement name="is_auth_ok_by_user" resultType="bool">
			<![CDATA[
			select exists
			(select su.user_id as userId,
			su.`name` as userName,
			sa.action_page as actionPage
			from sec_user as su
			join sec_user_role  as sur  on su.user_id =sur.user_id
			join sec_role as sr on sur.role_id = sr.role_id 
			join sec_role_action as sra on sr.role_id = sra.role_id
			join sec_action as sa on sra.action_id = sa.action_id
			where su.user_id = #user_id#
			and sa.seq = #action_seq#);
			]]>
			<parameter name="user_id" type="string" />
			<parameter name="action_seq" type="int" />
		</statement>

		<statement name="is_user_in_role" resultType="bool">
			<![CDATA[
			select exists
			(select su.user_id as userId,
			su.`name` as userName,
            sr.`display_name` as roleName,
            sr.role_seq as roleSeq	
			from sec_user as su
			join sec_user_role  as sur  on su.user_id =sur.user_id
			join sec_role as sr on sur.role_id = sr.role_id
			where su.user_id = #user_id#
			and sr.role_seq = #role_seq#);
			]]>
			<parameter name="user_id" type="string" />
			<parameter name="role_seq" type="int" />
		</statement>

		<statement name="find_auth_roles_by_user" resultType="table">
			<![CDATA[
			select 
			su.user_id as userId,
			su.`name` as userName,
			sr.role_id as roleId,
			sr.display_name as roleName
			from sec_user as su
			join sec_user_role  as sur  on su.user_id =sur.user_id
			join sec_role as sr on sur.role_id = sr.role_id 
			where su.user_id=#user_id#
			order by convert(roleName using gbk)
			]]>
			<parameter name="user_id" type="string" />
		</statement>

		<statement name="find_user_info_by_dep_id" resultType="table">
			<![CDATA[
			select 
			sue.user_id as userId,
			sue.dep_id as depId,
			su.`name` as userName,
			su.`real_name` as userRealName,
			sc.`value` as depName
			from sec_user_ext as sue
			join sec_user as su on sue.user_id = su.user_id 
			join sys_code as sc on sue.dep_id =sc.key and sc.`type`=40
			where sue.dep_id=#dep_id#
			order by convert(userName using gbk)
			]]>
			<parameter name="dep_id" type="string" />
		</statement>

		<statement name="create_role_action">
			<![CDATA[
			insert into sec_role_action
			(role_id,action_id)
			values(#role_id#,#action_id#);
			]]>
			<parameter name="role_id" type="string" />
			<parameter name="action_id" type="string" />
		</statement>

		<statement name="delete_role_action">
			<![CDATA[
			delete from  sec_role_action where role_id=#role_id#;
			]]>
			<parameter name="role_id" type="string" />
		</statement>

		<statement name="find_role_by_id" resultType="table" resultTable="role" description="获取指定角色ID所关联的角色信息，包括其可访权限信息">
			<![CDATA[
			select  
			sr.role_id as roleId,
			sr.role_seq as roleSeq,
			group_concat(sra.action_id) as roleActions,
			display_name as displayName,
			description
			from sec_role  as sr
			left join sec_role_action as sra on sr.role_id=sra.role_id 
			where sr.role_id = #role_id#
			group by sr.role_id
			limit 1
			]]>
			<parameter name="role_id" type="string" />
		</statement>

		<statement name="validate_duplicated_role" resultType="bool" >
			<![CDATA[
			select exists(
			select sec_role.role_id roleId from sec_role
			where sec_role.display_name = #display_name#
			and sec_role.role_id <> #role_id#)
			]]>
			<parameter name="display_name" type="string" />
			<parameter name="role_id" type="string" />
		</statement>

		<statement name="validate_using_role" resultType="bool" >
			<![CDATA[
			select exists(
			select user_id from sec_user_role
			where role_id=#role_id#)
			]]>
			<parameter name="role_id" type="string" />
		</statement>

		<statement name="create_role">
			<![CDATA[
			insert into sec_role(role_id, role_seq, display_name, description)
			select #role_id#, ifnull(max(role_seq), 0) + 1,	#display_name#,	#description# from sec_role;
			]]>
			<parameter name="role_id" type="string" />
			<parameter name="display_name" type="string" />
			<parameter name="description" type="string" />
		</statement>

		<statement name="delete_role">
			<![CDATA[
			delete from sec_role_action where role_id=#role_id#;
			delete from  sec_role where role_id=#role_id#;			
			]]>
			<parameter name="role_id" type="string" />
		</statement>

		<statement name="update_role">
			<![CDATA[
			update sec_role
			set display_name = #display_name#,
			description = #description#
			where role_id = #role_id#
			]]>
			<parameter name="role_id" type="string" />
			<parameter name="display_name" type="string" />
			<parameter name="description" type="string" />
		</statement>

		<statement name="find_role_info" allowPaging="true" resultType="table" description="根据条件匹配角色信息,包括角色可访问权限信息等">
			<![CDATA[
			select
			sr.role_id as roleId,
			sr.display_name as roleName,
			sr.description as roleDescription,
			group_concat(sa.action_id) as actionId,
			group_concat(sa.`seq`) as actionSeq,
			group_concat(sa.`name`) as actionNames
			from sec_role as sr
			left join sec_role_action as sra on sr.role_id = sra.role_id
			left join sec_action as sa on sra.action_id = sa.action_id
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="role_name">
					<![CDATA[
					sr.display_name like concat('%', #role_name#, '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="role_description">
					<![CDATA[
					sr.description like concat('%', #role_description#, '%')
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
			group by sr.role_id,sr.display_name,sr.description
			order by convert(roleName using gbk)
			]]>
			<parameter name="role_name" type="string" matchType="contains"/>
			<parameter name="role_description" type="string" matchType="contains"/>
		</statement>

		<statement name="find_role_info_count"  resultType="int">
			<![CDATA[
			select
			count(distinct sr.role_id)
			from sec_role as sr
			left join sec_role_action as sra on sr.role_id = sra.role_id
			left join sec_action as sa on sra.action_id = sa.action_id
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="role_name">
					<![CDATA[
					sr.display_name like concat('%', #role_name#, '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="role_description">
					<![CDATA[
					sr.description like concat('%', #role_description#, '%')
					]]>
				</isNotEmpty>
			</dynamic>
			<parameter name="role_name" type="string" matchType="contains"/>
			<parameter name="role_description" type="string" matchType="contains"/>
		</statement>

		<statement name="create_sys_log">
			<![CDATA[
			insert into sys_log
			(log_id, user_id,operation,operand,operand_name,datepoint,remark,real_name)
			select #log_id#,#user_id#,#operation#,#operand#,p.name,#datepoint#,#remark#,''
			from project as p where  p.project_id=#operand#;
			update sys_log set real_name=(select real_name from sec_user where user_id=#user_id#)
			where log_id=#log_id#;
			]]>
			<parameter name="log_id" type="string" />
			<parameter name="user_id" type="string" />
			<parameter name="operation" type="string" />
			<parameter name="operand" type="string" />
			<parameter name="datepoint" type="datetime" />
			<parameter name="remark" type="string" />
		</statement>

		<statement name="find_sys_log" resultType="table" allowPaging="true" >
			<![CDATA[
			select 
			sl.log_id as logId, 
			sl.real_name as userName,
			sl.operation,
			ifnull(p.name,sl.operand_name) as projectName,
			sl.operand,
			sl.datepoint,
			sl.remark
			from
			sys_log as sl 
			left join project as p on sl.operand = p.project_id
			left join sec_user as su on sl.user_id = su.user_id 
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="project_name">
					<![CDATA[
					p.`name` like concat('%',#project_name#,'%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="real_name">
					<![CDATA[
					sl.real_name like concat('%',#real_name#,'%')
					]]>
				</isNotEmpty>
				<isNotNull parameter="min_date">
					<![CDATA[
					sl.datepoint >= #min_date#
					]]>
				</isNotNull>
				<isNotNull parameter="max_date">
					<![CDATA[
					sl.datepoint <= #max_date#
					]]>
				</isNotNull>
			</dynamic>
			<![CDATA[
			order by datepoint desc
			]]>
			<parameter name="project_name" type="string" matchType="contains"/>
			<parameter name="real_name" type="string" matchType="contains"/>
			<parameter name="min_date" type="date" />
			<parameter name="max_date" type="endDate" />
		</statement>

		<statement name="find_sys_log_count" resultType="int" >
			<![CDATA[
			select 
			count(*)			
			from
			sys_log as sl 
			left join project as p on sl.operand = p.project_id
			left join sec_user as su on sl.user_id = su.user_id 
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="project_name">
					<![CDATA[
					p.`name` like concat('%',#project_name#,'%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="real_name">
					<![CDATA[
					sl.real_name like concat('%',#real_name#,'%')
					]]>
				</isNotEmpty>
				<isNotNull parameter="min_date">
					<![CDATA[
					sl.datepoint >= #min_date#
					]]>
				</isNotNull>
				<isNotNull parameter="max_date">
					<![CDATA[
					sl.datepoint <= #max_date#
					]]>
				</isNotNull>
			</dynamic>
			<parameter name="project_name" type="string" matchType="contains"/>
			<parameter name="real_name" type="string" matchType="contains"/>
			<parameter name="min_date" type="date" />
			<parameter name="max_date" type="endDate" />
		</statement>
	</module>
</root>
